﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;

namespace Supermarket.Dal
{
    public static class InquireDB
    {
        public static List<ViewAllProductsInStock> GetAllProducts(int categoryId)
        {
            DataClassesSupermarketDataContext db = new DataClassesSupermarketDataContext();

            if (categoryId == -1)
            {
                var products =
                    from prod in db.ViewAllProductsInStocks
                    select prod;

                return products.ToList<ViewAllProductsInStock>();
            }
            else
            {
                var products =
                    from prod in db.ViewAllProductsInStocks
                    where prod.CategoryId == categoryId
                    select prod;

                return products.ToList<ViewAllProductsInStock>();
            }
        }

        public static List<ViewSale> GetAllSales(DateTime fromDate, DateTime toDate)
        {
            DataClassesSupermarketDataContext db = new DataClassesSupermarketDataContext();

            var sales =
                from sale in db.ViewSales
                where (sale.SaleOn.Date >= fromDate.Date) && (sale.SaleOn.Date <= toDate.Date)
                select sale;

            return sales.ToList<ViewSale>();
        }

        public static double GetSaleQuantity(int productId, DateTime fromDate)
        {
            DataClassesSupermarketDataContext db = new DataClassesSupermarketDataContext();

            try
            {
                var quantity = (
                    from s in db.Sales
                    join p in db.ProductsInSales on s.SaleId equals p.SaleId
                    where (s.SaleOn.Date >= fromDate.Date) && (p.ProductId == productId)
                    select p.Quantity).Sum();

                return quantity;
            }
            catch (InvalidOperationException)
            {
                return 0.0;
            }
        }

        public static double GetSupplyQuantity(int productId, DateTime fromDate)
        {
            DataClassesSupermarketDataContext db = new DataClassesSupermarketDataContext();

            try
            {
                var quantity = (
                    from s in db.Supplies
                    join p in db.ProductsInSupplies on s.SupplyId equals p.SupplyId
                    where (s.SupplyOn.Date >= fromDate.Date) && (p.ProductId == productId)
                    select p.Quantity).Sum();
                
                return quantity;
            }
            catch (InvalidOperationException)
            {
                return 0.0;
            }            
        }
    }
}
